package com.cc.dao.impl;

import com.cc.dao.BaseDao;
import com.cc.dao.TransactionsDao;
import com.cc.entity.Page;
import com.cc.entity.Transactions;
import com.cc.util.DateUtil;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class TransactionsDaoImpl extends BaseDao implements TransactionsDao {

    @Override
    public Page<Transactions> getTransactionsList(Page<Transactions> page, int userId) {
        List<Transactions> transactionsList=new ArrayList<>();
        //sql指令
        StringBuffer sb=new StringBuffer();
        sb.append(" SELECT * ");
        sb.append(" FROM transactions ");
        sb.append(" WHERE 1=1 ");
        if (userId==0||!"".equals(userId)){
            sb.append(" AND user_id= "+userId);
        }
        sb.append(" LIMIT "+(page.getPageNum()-1)*page.getPageSize()+","+page.getPageSize());
        ResultSet rs=executeQuery(sb.toString());
        if (rs!=null){
            try {
                while (rs.next()){
                    Transactions transactions=new Transactions();
                   transactions.setId(rs.getInt("id"));
                   transactions.setUser_id(rs.getInt("user_id"));
                   transactions.setAccount_id(rs.getInt("account_id"));
                   transactions.setCategory_id(rs.getInt("category_id"));
                   transactions.setAmount(rs.getDouble("amount"));
                   transactions.setType(rs.getString("type"));
                   transactions.setDescription(rs.getString("description"));
                   transactions.setTransaction_date(rs.getDate("transaction_date"));
                   transactions.setCreated_at(rs.getTimestamp("created_at"));
                   transactionsList.add(transactions);
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            } finally {
                closedAll(rs);
            }
        }
        page.setData(transactionsList);
        page.setTotal(getTransactionsCount(userId));
        return page;
    }

    @Override
    public int getTransactionsCount(int userId) {
        int count = 0;
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT count(1) count");
        sb.append(" FROM transactions ");
        sb.append(" WHERE 1=1 ");
        if (!"".equals(userId)) {
            sb.append(" AND user_id="+userId);
        }
        ResultSet rs = executeQuery(sb.toString());
        if (rs != null) {
            try {
                while (rs.next()) {
                    count = rs.getInt("count");
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            } finally {
                closedAll(rs);
            }
        }
        return count;
    }

    @Override
    public int edit(Transactions transactions) {
        String sql = "UPDATE transactions SET user_id=?,account_id=?,category_id=?,amount=?,type=?,description=?,transaction_date=?,created_at=? WHERE id=?";
        return executeUpdate(sql, transactions.getUser_id(),transactions.getAccount_id(),transactions.getCategory_id(),
                transactions.getAmount(),transactions.getType(),transactions.getDescription(),transactions.getTransaction_date(),transactions.getCreated_at()
                );
    }

    @Override
    public int insert(Transactions transactions) {
        String sql = "INSERT INTO transactions(user_id,account_id,category_id,amount,type,description,transaction_date,created_at) VALUES(?,?,?,?,?,?,?,?)";
        return executeUpdate(sql,
                transactions.getUser_id(),transactions.getAccount_id(),transactions.getCategory_id(),
                transactions.getAmount(),transactions.getType(),transactions.getDescription(),transactions.getTransaction_date(),transactions.getCreated_at()
                );
    }

    /**
     * 根据指定的ID删除transactions表中的记录
     *
     * @param id 要删除的记录的ID
     * @return 返回executeUpdate方法的结果，表示受影响的行数
     */
    @Override
    public int delete(int id) {
        // 定义SQL语句，用于从transactions表中删除指定ID的记录
        String sql = "DELETE FROM transactions WHERE id=?";
        // 调用executeUpdate方法执行SQL语句，并传入id参数
        return executeUpdate(sql, id);
    }

}
